--BACKUP SERVICE MASTER KEY
--======================================================================================
--BACKUP SERVICE MASTER KEY TO FILE = 'D:\ShareLoc\CXTest.SMK'
--ENCRYPTION BY PASSWORD = 'cxVirtualSmKey'
--======================================================================================
--CREATION KEYS
--========================================================================================
--create one Database Master Key
Create master key
Encryption by Password = 'c0mpar58pr5ssDmKey'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

--Create One Certificate
Create Certificate CXUserCert
With Subject= 'Certificate to encrypt tblCEUser table',
Start_date = '2010-09-02',
Expiry_date ='2099-12-31'
GO

--Create Symmetric Key
Create Symmetric Key CXUserSym
With KEY_SOURCE = 'This is key source for CXUserSym',
IDENTITY_VALUE = 'This is identity value for CXUserSym',
Algorithm = AES_256
Encryption by Certificate CXUserCert

ALTER Table tblCEUser ADD
EInfo1 varbinary(256),
EInfo2 varbinary(256),
EInfo3 varbinary(256)

ALTER Table tblCEUsrProfile ADD
EInfo1 varbinary(256),
EInfo2 varbinary(256),
EInfo3 varbinary(256),
EInfo4 varbinary(256),
EInfo5 varbinary(256)

ALTER Table tblCEContact ADD
EInfo1 varbinary(256),
EInfo2 varbinary(256),
EInfo3 varbinary(256),
EInfo4 varbinary(256),
EInfo5 varbinary(256)

--GO
----==========================================================================================
----TESTING
----==========================================================================================
----open symmetric key
Open Symmetric key CXUserSym
Decryption by certificate CXUserCert
GO

--Select EncryptByKey(Key_Guid(N'CXUserSym'), 'phyokyawthant@gmail.com')

--Update tblCEUser Set EInfo1 = EncryptByKey(Key_Guid(N'CXUserSym'), 'phyokyawthant@gmail.com')
--Where CEID = 138

Select EInfo1 From tblCEUser Where CEID = 138
Select CONVERT(varchar(50), DECRYPTBYKEY(EInfo1)) As Email from tblCEUser Where CEID = 138

Close Symmetric key CXUserSym

----Select DecryptByKey('0x00F69A50AC495E4ABF862E962E49B57701000000500FA6115BE2CC1BE091DAAE0F07FEDB2609485DBC13B01B01FEAC7522522B1D80AC9AB6706F3F7BE48EBB3E807DB30C')


--================================================================================
--BACKING UP KEYS
--================================================================================
--MASTER KEY BACK UP
--================================================================================
--you are trying to restore database in different instance
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

--again opening master key before taking up backup
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'c0mpar58pr5ssDmKey'

--backing up master key to physical file
BACKUP MASTER KEY TO FILE = 'D:\SharedLoc\CX_DB_Live_DMK.DMK'
ENCRYPTION BY PASSWORD= 'c0mpar58pr5ssDmKey'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--=================================================================================
--CERTIFICATE BACK UP
--=================================================================================
BACKUP CERTIFICATE CXUserCert
TO FILE = 'D:\SharedLoc\CXLive_CX_DB_LiveUserCert.CER'
WITH PRIVATE KEY
(
      FILE='D:\SharedLoc\CXLive_CX_DB_LiveUserCert.PVK',
      ENCRYPTION BY PASSWORD='CXLiveUserCertKey'
)

--go
----====================================================================================
----RESTORING KEYS
----====================================================================================
----MASTER KEY RESTORE
----====================================================================================
----you need to open master key
--OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@Ss28'
--GO
----put the service level encryption back
--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--GO
----restore your Database master key from file
--RESTORE MASTER KEY FROM FILE = 'C:\Encry1.DMK'
--DECRYPTION BY PASSWORD = 'cxVirtualDmKey'
--ENCRYPTION BY PASSWORD= 'cxVirtualDmKey' 
--GO
----=====================================================================================
----RESTORE CERTIFICATE
----=====================================================================================
--DROP CERTIFICATE CXUSERCERT

--CREATE CERTIFICATE CXUSERCERT
--FROM FILE = 'D:\SHARELOC\CXTestUserCert.CER'

--ALTER CERTIFICATE AdvCert 
--    WITH PRIVATE KEY (FILE = 'D:\SHARELOC\CXTestUserCert.PVK',
--    DECRYPTION BY PASSWORD = 'CXTestUserCertKey');
--GO